/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2012                    */
/* Created on:     2020-04-16 10:06:03                          */
/*==============================================================*/


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('action') and o.name = 'FK_ACTION_ACTION_FO_POST')
alter table action
   drop constraint FK_ACTION_ACTION_FO_POST
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('channel_admin') and o.name = 'FK_CHANNEL__CHANNEL_A_USER')
alter table channel_admin
   drop constraint FK_CHANNEL__CHANNEL_A_USER
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('channel_admin') and o.name = 'FK_CHANNEL__CHANNEL_A_CHANNEL')
alter table channel_admin
   drop constraint FK_CHANNEL__CHANNEL_A_CHANNEL
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('comment') and o.name = 'FK_COMMENT_COMMENT_O_POST')
alter table comment
   drop constraint FK_COMMENT_COMMENT_O_POST
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('postBy') and o.name = 'FK_POSTBY_POSTBY_POST')
alter table postBy
   drop constraint FK_POSTBY_POSTBY_POST
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('postBy') and o.name = 'FK_POSTBY_POSTBY2_USER')
alter table postBy
   drop constraint FK_POSTBY_POSTBY2_USER
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('postBy') and o.name = 'FK_POSTBY_POSTBY3_CHANNEL')
alter table postBy
   drop constraint FK_POSTBY_POSTBY3_CHANNEL
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('subscribe') and o.name = 'FK_SUBSCRIB_SUBSCRIBE_CHANNEL')
alter table subscribe
   drop constraint FK_SUBSCRIB_SUBSCRIBE_CHANNEL
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('subscribe') and o.name = 'FK_SUBSCRIB_SUBSCRIBE_USER')
alter table subscribe
   drop constraint FK_SUBSCRIB_SUBSCRIBE_USER
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('action')
            and   name  = 'action_for_post_FK'
            and   indid > 0
            and   indid < 255)
   drop index action.action_for_post_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('action')
            and   type = 'U')
   drop table action
go

if exists (select 1
            from  sysobjects
           where  id = object_id('channel')
            and   type = 'U')
   drop table channel
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('channel_admin')
            and   name  = 'channel_admin2_FK'
            and   indid > 0
            and   indid < 255)
   drop index channel_admin.channel_admin2_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('channel_admin')
            and   name  = 'channel_admin_FK'
            and   indid > 0
            and   indid < 255)
   drop index channel_admin.channel_admin_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('channel_admin')
            and   type = 'U')
   drop table channel_admin
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('comment')
            and   name  = 'comment_on_post_FK'
            and   indid > 0
            and   indid < 255)
   drop index comment.comment_on_post_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('comment')
            and   type = 'U')
   drop table comment
go

if exists (select 1
            from  sysobjects
           where  id = object_id('post')
            and   type = 'U')
   drop table post
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('postBy')
            and   name  = 'postBy3_FK'
            and   indid > 0
            and   indid < 255)
   drop index postBy.postBy3_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('postBy')
            and   name  = 'postBy2_FK'
            and   indid > 0
            and   indid < 255)
   drop index postBy.postBy2_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('postBy')
            and   name  = 'postBy_FK'
            and   indid > 0
            and   indid < 255)
   drop index postBy.postBy_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('postBy')
            and   type = 'U')
   drop table postBy
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('subscribe')
            and   name  = 'subscribe2_FK'
            and   indid > 0
            and   indid < 255)
   drop index subscribe.subscribe2_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('subscribe')
            and   name  = 'subscribe_FK'
            and   indid > 0
            and   indid < 255)
   drop index subscribe.subscribe_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('subscribe')
            and   type = 'U')
   drop table subscribe
go

if exists (select 1
            from  sysobjects
           where  id = object_id('"user"')
            and   type = 'U')
   drop table "user"
go

/*==============================================================*/
/* Table: action                                                */
/*==============================================================*/
create table action (
   post_id              bigint               not null,
   action_name          nvarchar(20)          null,
   hit_num              int                  null
)
go

/*==============================================================*/
/* Index: action_for_post_FK                                    */
/*==============================================================*/
create index action_for_post_FK on action (
post_id ASC
)
go

/*==============================================================*/
/* Table: channel                                               */
/*==============================================================*/
create table channel (
   channel_id           bigint               not null,
   channel_name         nvarchar(50)          null,
   channel_description  text                 null,
   channel_avtar_link   text                 null,
   constraint PK_CHANNEL primary key nonclustered (channel_id)
)
go

/*==============================================================*/
/* Table: channel_admin                                         */
/*==============================================================*/
create table channel_admin (
   user_ID              bigint               not null,
   channel_id           bigint               not null,
   constraint PK_CHANNEL_ADMIN primary key (user_ID, channel_id)
)
go

/*==============================================================*/
/* Index: channel_admin_FK                                      */
/*==============================================================*/
create index channel_admin_FK on channel_admin (
user_ID ASC
)
go

/*==============================================================*/
/* Index: channel_admin2_FK                                     */
/*==============================================================*/
create index channel_admin2_FK on channel_admin (
channel_id ASC
)
go

/*==============================================================*/
/* Table: comment                                               */
/*==============================================================*/
create table comment (
   comment_id           bigint               not null,
   post_id              bigint               not null,
   comment_msg          text                 null,
   comment_date         datetime             null,
   comment_upvote       int                  null,
   comment_downvote     int                  null,
   constraint PK_COMMENT primary key nonclustered (comment_id)
)
go

/*==============================================================*/
/* Index: comment_on_post_FK                                    */
/*==============================================================*/
create index comment_on_post_FK on comment (
post_id ASC
)
go

/*==============================================================*/
/* Table: post                                                  */
/*==============================================================*/
create table post (
   post_id              bigint               not null,
   post_msg             nvarchar(1000)        null,
   post_date            datetime             null,
   enable_comment       bit                  null,
   enable_action        bit                  null,
   post_upvote          int                  null,
   post_downvote        int                  null,
   constraint PK_POST primary key nonclustered (post_id)
)
go

/*==============================================================*/
/* Table: postBy                                                */
/*==============================================================*/
create table postBy (
   post_id              bigint               not null,
   user_ID              bigint               not null,
   channel_id           bigint               not null,
   is_repost            bit                  null,
   origin_channel_id    bigint               null,
   constraint PK_POSTBY primary key (post_id, user_ID, channel_id)
)
go

/*==============================================================*/
/* Index: postBy_FK                                             */
/*==============================================================*/
create index postBy_FK on postBy (
post_id ASC
)
go

/*==============================================================*/
/* Index: postBy2_FK                                            */
/*==============================================================*/
create index postBy2_FK on postBy (
user_ID ASC
)
go

/*==============================================================*/
/* Index: postBy3_FK                                            */
/*==============================================================*/
create index postBy3_FK on postBy (
channel_id ASC
)
go

/*==============================================================*/
/* Table: subscribe                                             */
/*==============================================================*/
create table subscribe (
   channel_id           bigint               not null,
   user_ID              bigint               not null,
   constraint PK_SUBSCRIBE primary key (channel_id, user_ID)
)
go

/*==============================================================*/
/* Index: subscribe_FK                                          */
/*==============================================================*/
create index subscribe_FK on subscribe (
channel_id ASC
)
go

/*==============================================================*/
/* Index: subscribe2_FK                                         */
/*==============================================================*/
create index subscribe2_FK on subscribe (
user_ID ASC
)
go

/*==============================================================*/
/* Table: "user"                                                */
/*==============================================================*/
create table "user" (
   user_ID              bigint               not null,
   user_nickname        nvarchar(50)          null,
   user_pwd             nvarchar(50)          null,
   user_avtar_link      text                 null,
   user_detail          text                 null,
   user_reserve1        nvarchar(50)          null,
   constraint PK_USER primary key nonclustered (user_ID)
)
go

alter table action
   add constraint FK_ACTION_ACTION_FO_POST foreign key (post_id)
      references post (post_id)
go

alter table channel_admin
   add constraint FK_CHANNEL__CHANNEL_A_USER foreign key (user_ID)
      references "user" (user_ID)
go

alter table channel_admin
   add constraint FK_CHANNEL__CHANNEL_A_CHANNEL foreign key (channel_id)
      references channel (channel_id)
go

alter table comment
   add constraint FK_COMMENT_COMMENT_O_POST foreign key (post_id)
      references post (post_id)
go

alter table postBy
   add constraint FK_POSTBY_POSTBY_POST foreign key (post_id)
      references post (post_id)
go

alter table postBy
   add constraint FK_POSTBY_POSTBY2_USER foreign key (user_ID)
      references "user" (user_ID)
go

alter table postBy
   add constraint FK_POSTBY_POSTBY3_CHANNEL foreign key (channel_id)
      references channel (channel_id)
go

alter table subscribe
   add constraint FK_SUBSCRIB_SUBSCRIBE_CHANNEL foreign key (channel_id)
      references channel (channel_id)
go

alter table subscribe
   add constraint FK_SUBSCRIB_SUBSCRIBE_USER foreign key (user_ID)
      references "user" (user_ID)
go

